/**
 * 
 */
package com.goktech.commons.excel.utils.writer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import com.goktech.commons.db.SQL;
import com.goktech.commons.excel.utils.abs.AbstractWriter;

/**
 * @author Administrator 从数据库导出数据
 */
public class ExcelJdbcExport extends AbstractWriter<ExcelJdbcExport> {

	private String driver;
	private String user;
	private String passwd;
	private String url;
	private String sql;
	private List<String> titles;
	public ExcelJdbcExport(String driver, String user, String passwd, String url) {
		super();
		this.driver = driver;
		this.user = user;
		this.passwd = passwd;
		this.url = url;
	}

	/**
	 * 需要导出的sql
	 * 
	 * @param sql
	 * @return
	 */
	public ExcelJdbcExport sql(SQL sql) {
		this.sql = sql.toString();
		this.titles = sql.getFieldList();
		setTitle(titles);
		return this;
	}

	public ExcelJdbcExport executeQuery() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, passwd);
			ps = conn.prepareStatement(sql);
			ResultSet result = ps.executeQuery();
			resolve(result);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this;
	}

	/**
	 * 需要导出的sql
	 * 
	 * @param sql
	 * @param map
	 * @return
	 */
	public ExcelJdbcExport sql(String sql) {
		this.sql = sql;
		return this;
	}

	@Override
	public ExcelJdbcExport getSelf() {
		return this;
	}
	
	private void resolve(ResultSet result) throws SQLException {
		if(this.titles == null ||this.titles.size() == 0) {
			this.titles = new ArrayList<>();
			ResultSetMetaData metaData = result.getMetaData();
			for(int i = 1;i <= metaData.getColumnCount();i++) {
				titles.add(metaData.getColumnName(i));
			}
			if(this.titles.size() > 0) {
				setTitle(titles);
			}
		}
		List<Collection<Object>> content = new ArrayList<>();
		while(result.next()) {
			List<Object> list= new ArrayList<>();
			for(String key : this.titles) {
				list.add(result.getObject(key));
			}
			content.add(list);
		}
		super.setContent(content);
	}
}
